﻿CREATE PROCEDURE [dbo].[spRepl_ReinitFailingSubscriptions]
(
	@maxRetryAttempts tinyint = 5
)
AS

SET NOCOUNT ON;

DECLARE @tReinit TABLE ([database_name] sysname, [pub_name] sysname, [subscriber_name] sysname, [destination_dbname] sysname)
DECLARE @sReinit varchar(MAX), @sReinitTemplate varchar(MAX), @sSQL varchar(MAX)
SELECT	@sReinitTemplate = 'USE [[VAR_DBNAME]]; EXEC [dbo].[sp_reinitsubscription] @publication = ''[VAR_PUBNAME]'', @subscriber = ''[VAR_SUBSCRIBER]''',
		@sSQL = REPLACE('USE [?];
IF OBJECT_ID(''dbo.syspublications'') IS NOT NULL
	SELECT		DISTINCT
				DB_NAME() database_name,
				p.name,
				s.srvname,
				s.dest_db
	FROM		dbo.syspublications p
	JOIN		dbo.sysarticles a ON p.pubid = a.pubid
	JOIN		dbo.syssubscriptions s ON a.artid = s.artid
	JOIN		(
				SELECT		*
				FROM		msdb.dbo.sysjobhistory
				WHERE		instance_id IN (
							SELECT		MAX(instance_id) instance_id
							FROM		msdb.dbo.sysjobhistory jh
							GROUP BY	job_id,
										step_id
							)
							AND retries_attempted > [VAR_RETRIES]
				) jh ON s.distribution_jobid = jh.job_id;', '[VAR_RETRIES]', CAST(@maxRetryAttempts as varchar(100)))

INSERT INTO @tReinit EXEC sp_MSforeachdb @sSQL

DECLARE crsReinit CURSOR FAST_FORWARD READ_ONLY FOR
	SELECT	REPLACE(
				REPLACE(
					REPLACE(@sReinitTemplate, '[VAR_DBNAME]', [database_name])
				, '[VAR_PUBNAME]', [pub_name])
			, '[VAR_SUBSCRIBER]', [subscriber_name])
	FROM	@tReinit
OPEN crsReinit
FETCH NEXT FROM crsReinit INTO @sReinit
WHILE @@FETCH_STATUS <> -1
BEGIN
	BEGIN TRY
		EXEC	(@sReinit)
	END TRY
	BEGIN CATCH
		PRINT ''
	END CATCH
	FETCH NEXT FROM crsReinit INTO @sReinit
END
CLOSE crsReinit
DEALLOCATE crsReinit

SET NOCOUNT OFF;
